package com.weiyuwqj.api.util;


import com.weiyuwqj.api.model.convert.DefaultConvert;
import com.weiyuwqj.api.model.convert.ModelConvert;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by Administrator on 2016/3/8.
 */
public class ExcelUtils {

    //导出
    public String export(Map<Integer, Object> titleMap, List<Map> contentList, String name) {
        String path = "D:/java/" + name + ".xlsx";//需要导出的目录路径
        XSSFWorkbook workbook = new XSSFWorkbook();//创建workbook
        XSSFSheet sheet = workbook.createSheet();//创建电子表格
        XSSFRow row;
        XSSFCell cell;
        XSSFCellStyle style = workbook.createCellStyle();//创建格式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//设置居中
        Set<Integer> keySet;
        /*
         *  遍历标题list，将数据写到workbook中
         *  遍历内容list，将数据写到workbook中
         */
        if (contentList != null) {
            short i = 0;
            for (Map map : contentList) {
                short j = 0;
                if (titleMap != null) {
                    keySet = titleMap.keySet();
                    if (i == 0) {
                        row = sheet.createRow(i++);
                        for (Integer title : keySet) {
                            cell = row.createCell((short) j++);
                            cell.setCellStyle(style);
                            cell.setCellValue((String) titleMap.get(title));
                            System.out.println(title);
                        }
                        j = 0;
                    }
                    row = sheet.createRow(i++);
                    for (Integer title : keySet) {
                        cell = row.createCell((short) j++);
                        cell.setCellStyle(style);
                        cell.setCellValue((String) map.get(title));
                        System.out.println(title);

                    }
                }
            }
        }
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(path));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(fos);//将workbook通过流写到文件
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }

    //导出
    public String export(List<String> titleList, List<Map> contentList, String name) {
        String path = "D:/java/" + name + ".xlsx";//需要导出的目录路径
        XSSFWorkbook workbook = new XSSFWorkbook();//创建workbook
        XSSFSheet sheet = workbook.createSheet();//创建电子表格
        XSSFRow row;
        XSSFCell cell;
        XSSFCellStyle style = workbook.createCellStyle();//创建格式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//设置居中
        /*
         *  遍历标题list，将数据写到workbook中
         *  遍历内容list，将数据写到workbook中
         */
        if (contentList != null) {
            short i = 0;
            for (Map map : contentList) {
                short j = 0;
                if (titleList != null) {
                    if (i == 0) {
                        row = sheet.createRow(i++);
                        for (String title : titleList) {
                            cell = row.createCell((short) j++);
                            cell.setCellStyle(style);
                            cell.setCellValue(title);
                            System.out.println(title);
                        }
                        j = 0;
                    }
                    row = sheet.createRow(i++);
                    for (String title : titleList) {
                        cell = row.createCell((short) j++);
                        cell.setCellStyle(style);
                        cell.setCellValue((String) map.get(title));
                        System.out.println(title);

                    }
                }
            }
        }
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(path));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(fos);//将workbook通过流写到文件
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }

    //将map转化为有序的list
    public static List mapToList(List eList, List convertList, Map<String, Object> map) {
        List retList = new ArrayList(eList.size());
        for (int i = 0; i < eList.size(); i++) {
            Object val = map.get(eList.get(i));
            Object convertObj = convertList.get(i);
            if (convertObj == null) convertObj = new DefaultConvert();
            ModelConvert c = (ModelConvert) convertObj;
            Object convertVal = c.convert(val);
            retList.add(convertVal);
        }
        return retList;
    }

    //导出
    public static String export(List<List> content, String dir) {
        XSSFWorkbook workbook = new XSSFWorkbook();//创建workbook
        XSSFSheet sheet = workbook.createSheet();//创建电子表格
        XSSFRow row;
        XSSFCell cell;
        XSSFCellStyle style = workbook.createCellStyle();//创建格式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//设置居中
        int i = 0;
        for (List oneContent : content) {
            row = sheet.createRow(i++);
            int j = 0;
            for (Object oneRecord : oneContent) {
                cell = row.createCell(j++);
                cell.setCellStyle(style);
                if (oneRecord == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(oneRecord.toString());
                }
            }
        }
        File dirFile = new File(dir);
        if (!dirFile.exists()) {
            dirFile.mkdir();
        }
        String path = DateUtils.getDate("yyyyMMdd") + RandomCodesUtils.createRandom(true, 5) + ".xls";
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(dir + "/" + path));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(fos);//将workbook通过流写到文件
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }


    //将object转化为list
    public static List ObjectToList(List<String> filedList,
                                    List<ModelConvert> convertList,
                                    List objectList) {
        List<List> allList = new ArrayList<>();
        //遍历每个对象将对象中的属性的赋值给allList
        for (Object object : objectList) {

            List retList = new ArrayList(filedList.size());
            for (int i = 0; i < filedList.size(); i++) {
                try {
                    String getMethodName = "get" + filedList.get(i);
                    Method getMethod = object.getClass().getMethod(getMethodName);//获取get方法
                    Object val = getMethod.invoke(object);

                    Object convertObj = convertList.get(i);
                    if (convertObj == null) convertObj = new DefaultConvert();
                    ModelConvert c = (ModelConvert) convertObj;
                    Object convertVal = c.convert(val);
                    retList.add(convertVal);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            allList.add(retList);
        }
        return allList;
    }

    //导出
    public static String export(String title,
                                List<String> titleList,
                                List<String> filedList,
                                List<ModelConvert> convertList,
                                List objectList,
                                String dir) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.WHITE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        HSSFCellStyle style3 = workbook.createCellStyle();
        style3.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
        style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体

        // 把字体应用到当前的样式
        style3.setFont(font2);

        HSSFFont font3 = workbook.createFont();
        font3.setColor(HSSFColor.BLACK.index);
        font3.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);



      /*  // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("汇诚报表"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("云复科技");*/

        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        for (short i = 0; i < titleList.size(); i++) {
            cell= row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(titleList.get(i));
            cell.setCellValue(text);
        }

        //把每个单元格赋值
        int index = 0;
        for (Object object : objectList) {
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < filedList.size(); i++) {
                try {
                    cell = row.createCell(i);
                    if (index % 2 == 1) {
                        cell.setCellStyle(style2);
                    } else {
                        cell.setCellStyle(style3);
                    }

                    String getMethodName = "get" + filedList.get(i);
                    Method getMethod = object.getClass().getMethod(getMethodName);//获取get方法
                    Object val = getMethod.invoke(object);

                    Object convertObj = convertList.get(i);
                    if (convertObj == null) convertObj = new DefaultConvert();
                    ModelConvert c = (ModelConvert) convertObj;
                    Object convertVal = c.convert(val);

                    if (convertVal != null) {
                        HSSFRichTextString richString = new HSSFRichTextString(convertVal.toString());
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }


                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        //生成表格
        File dirFile = new File(dir);
        if (!dirFile.exists()) {
            dirFile.mkdir();
        }
        String path = DateUtils.getDate("yyyyMMdd") + RandomCodesUtils.createRandom(true, 5) + ".xls";
        try {
            FileOutputStream fos = new FileOutputStream(new File(dir + "/" + path));
            workbook.write(fos);//将workbook通过流写到文件
            fos.flush();
            workbook.close();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }


    public static List<List> readExcel(InputStream is,String path, Integer cellNumber) {
        List<List> list = new ArrayList<>();
        try {
            if (path.endsWith(".xlsx")) {
                list = readXlsx(is, cellNumber);
            } else if (path.endsWith(".xls")) {
                list = readXls(is, cellNumber);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    //读取excel表格内容(excel2007以上)
    public static List<List> readXlsx(InputStream is, Integer cellNumber) throws IOException {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<List> list = new ArrayList<>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    List<String> rowList = new ArrayList<>();
                    for (int no = 0; no < cellNumber; no++) {
                        XSSFCell cell = xssfRow.getCell(no);
                        rowList.add(getValue(cell));
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    //读取excel表格内容(excel2003以下)
    public static List readXls(InputStream is, Integer cellNumber) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<List> list = new ArrayList<>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    List<String> rowList = new ArrayList<>();
                    for (int no = 0; no < cellNumber; no++) {
                        HSSFCell cell = hssfRow.getCell(0);
                        rowList.add(getValue(cell));
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    //获取单元格的值
    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    //获取单元格的值
    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
}
